package cn.com.dhcc.ddi.datasource.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.dao.SimpleDaoForOracle;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.exception.NiceException;
import cn.com.dhcc.app.core.exception.ServiceException;
import cn.com.dhcc.ddi.datasource.vo.DataSourceVo;
import cn.com.dhcc.ddi.datasource.vo.TableColVo;
import cn.com.dhcc.ddi.datasource.vo.TableInfoVo;
import cn.com.dhcc.ddi.datasource.vo.wapper.TableColRowWapper;
import cn.com.dhcc.ddi.datasource.vo.wapper.TableInfoRowWapper;
/**
 * 
 * DataSourceSQLserverDao
 * @Author: fxx
 * @date: 2017-5-16
 *
 */
@Repository
public class DataSourceSQLserverDao extends BaseDao {
	private SimpleDaoForOracle dao;
	@Autowired
	public DataSourceDao datasourceDao;
	/**
	 * 现根据界面上的连接信息初始化数据源
	 * 
	 * @param vo
	 * @throws NiceException
	 * @throws ServiceException 
	 * @throws DaoException 
	 */
	public void initDao(DataSourceVo vo) throws DaoException {
		try {
			dao = datasourceDao.initDao(vo);
		} catch (DaoException e) {
			throw new DaoException("初始化数据源错误", e);
		}
	}
	/**
	 * 初始化数据源
	 * @param dataSource
	 * @return
	 * @throws NiceException
	 */
	public SimpleDaoForOracle getDaoByPublishDataSource(DataSourceVo dataSource)  throws  DaoException{
		final SimpleDaoForOracle dao = new SimpleDaoForOracle();
		try {
			dao.initDataSource(dataSource.getDataSourceConfVo().getDriverName(), dataSource.getDataSourceConfVo().getUrl(), dataSource.getDataSourceConfVo().getUserName(), dataSource.getDataSourceConfVo().getPassword());
		} catch (Exception e) {
			throw new DaoException("初始化数据源失败", e);
		}

		return dao;
	}
	
	/**
	 * 
	 * @MethodName: [getUserTablesForSqlserver]
	 * @Description:[获取所有被授权的表]
	 * @Author:     [fxx]
	 * @CreateDate: [2017-5-16]
	 * @Params:     [ParamType ParamValue,ParamType ParamValue...]
	 * @See         [Related classes and methods]
	 * @Since       [Product template version]  
	 * @Return:     [List<TableInfoVo>]
	 * @Throws:     [Exception]
	 *
	 */
	public List<TableInfoVo> getUserTablesForSqlserver()throws DaoException {
		String msg = "";
		String sql ="select t1.table_name,t1.table_schema schema_name,t2.table_comment from " +
					"      (select table_name,table_schema from information_schema.TABLES ) t1 " + 
					"    left join " + 
					"      (select CONVERT(varchar(100), tbs.name) table_name,CONVERT(varchar(100),ds.value) table_comment from sys.extended_properties ds " + 
					"          left join sysobjects tbs on ds.major_id=tbs.id " + 
					"    where  ds.minor_id=0) t2 " + 
					"    on t1.table_name = t2.table_name order by t1.table_name ";
		try {
			return dao.list("获取所有被授权的表", sql, new TableInfoRowWapper(), new Object[] { });
		} catch (DaoException e) {
			msg = "获取所有表失败";
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 
	 * @MethodName: [getUserTabColumnsForSqlserver]
	 * @Description:[获取表元数据失败]
	 * @Author:     [fxx]
	 * @CreateDate: [2017-5-16]
	 * @Params:     [ParamType ParamValue,ParamType ParamValue...]
	 * @See         [Related classes and methods]
	 * @Since       [Product template version]  
	 * @Return:     [List<TableColVo>]
	 * @Throws:     [Exception]
	 *
	 */
	public List<TableColVo> getUserTabColumnsForSqlserver(String schema, String tableName) throws DaoException {
		String msg = "";
		String sql ="select t.* from ("+
				    "select distinct t1.column_name col_name,t1.data_type col_type,t1.column_default col_default, t2.column_comment col_comment,t3.is_pk " +
					"               from information_schema.COLUMNS  t1 " + 
					"               left join (select c.name column_name,cast(p.value as varchar(100)) column_comment " + 
					"                               from sys.extended_properties p ,sys.columns c WHERE  p.major_id=OBJECT_ID(?) and " + 
					"                               p.major_id=c.object_id and p.minor_id=c.column_id) t2 " +
					"                on t1.column_name =  t2.column_name " + 
					"               left join (select o.name as table_name,c.name as column_name,'1' as is_pk " + 
					"                           from sysindexes i " + 
					"                           join sysindexkeys k on i.id = k.id and i.indid = k.indid " + 
					"                           join sysobjects o on i.id = o.id " + 
					"                           join syscolumns c on i.id=c.id and k.colid = c.colid " + 
					"                           where exists(select 1 from sysobjects where xtype in ('PK','UQ') and name =  i.name)  and o.name=?) t3 " + 
					"                on  t1.column_name = t3.column_name " + 				
					"          where  t1.table_name=? "+
					" ) t order by t.is_pk desc, t.col_name";

		try {
			return dao.list("获取表元数据", sql, new TableColRowWapper(),
					new Object[] { schema+"."+tableName, tableName, tableName });
		} catch (DaoException e) {
			msg = "获取表元数据失败";
			throw new DaoException(msg, e);
		}
	}
	/**
	 * 获取数据库名称
	 * @return
	 * @throws DaoException
	 */
	public String getDBNameForSqlserver() throws DaoException {
		String msg = "";
		String sql = "select DB_NAME()";
		try {
			return dao.queryForString("获取数据库名称", sql, new Object[] {});
		} catch (DaoException e) {
			msg = "获取数据库名称失败";
			throw new DaoException(msg, e);
		}
	}
	
}
